package cn.gdcp.demod;

import android.database.Cursor;
import android.net.Uri;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;



public class MainActivity extends AppCompatActivity {
    private TextView android_average;
    private TextView android_max;
    private TextView android_max_name;
    private TextView android_min;
    private TextView android_min_name;
    private TextView english_average;
    private TextView english_min;
    private TextView english_max;
    private TextView english_min_name;
    private TextView english_max_name;
    private TextView total_score;
    private TextView total_max;
    private static final String TAG = "MainActivity";
    private TextView total_max_name;
    private TextView total_min;
    private TextView total_min_name;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        setTitle("分数统计");
        initBunder();
        getAndroidAverage();
        getAndroidMax();
        getAndroidMin();
        getEnglishAverage();
        getEnglishMax();
        getEnglishMin();
        getTotalScore();
        getTotalMax();
        getTotalMin();
    }

    private void getTotalMin() {
        Uri uri=Uri.parse("content://com.sql/score");
        Uri uri1=Uri.parse("content://com.sql/student");

        /* String sql="select stuno ,sum(english+android) AS s,stuno FROM score GROUP BY stuno ORDER BY s DESC ;";*/
        String arr[]={"stuno,sum(english+android) as s"};
        String where="android>0 GROUP BY stuno ORDER BY s ASC";
        Cursor cursor=getContentResolver().query(uri,arr,where,null,null);
        if (cursor!=null){
            cursor.moveToFirst();
            total_min.setText(String.valueOf(cursor.getInt(1)));
            String where1="stuno="+cursor.getString(0);
            Cursor cursor1=getContentResolver().query(uri1,null,where1,null,null);
            if (cursor1!=null){
                cursor1.moveToFirst();
                String name=cursor1.getString(1);
                total_min_name.setText(name);
            }
        }





    }

    private void getTotalMax() {
        Uri uri=Uri.parse("content://com.sql/score");
        Uri uri1=Uri.parse("content://com.sql/student");

       /* String sql="select stuno ,sum(english+android) AS s,stuno FROM score GROUP BY stuno ORDER BY s DESC ;";*/
       String arr[]={"stuno,sum(english+android) as s"};
       String where="android>0 GROUP BY stuno ORDER BY s DESC";
        Cursor cursor=getContentResolver().query(uri,arr,where,null,null);
        if (cursor!=null){
            cursor.moveToFirst();
            total_max.setText(String.valueOf(cursor.getInt(1)));
            String where1="stuno="+cursor.getString(0);
                  Cursor cursor1=getContentResolver().query(uri1,null,where1,null,null);
                  if (cursor1!=null){
                      cursor1.moveToFirst();
                      String name=cursor1.getString(1);
                      total_max_name.setText(name);
                  }
        }

    }

    private void getTotalScore() {
        Uri uri=Uri.parse("content://com.sql/score");
        Cursor cursor=getContentResolver().query(uri,null,null,null,null);
        int total=0;
        if (cursor!=null){
            for (cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
                total+=cursor.getInt(2)+cursor.getInt(1);
            }

        }
        float result= (float) (1.0*total/cursor.getCount());
        total_score.setText(String.valueOf(result));
    }

    private void getEnglishMin() {
        int english = 0;
        String name = null;
        Uri uri = Uri.parse("content://com.sql/score");
        Uri uri1 = Uri.parse("content://com.sql/student");
        Cursor cursor = getContentResolver().query(uri, null, null, null, "english " + "asc");

        if (cursor != null) {
            cursor.moveToFirst();
            english = cursor.getInt(2);
            String stuno = cursor.getString(0);
            String where = "stuno=" + stuno;
            Cursor cursor1 = getContentResolver().query(uri1, null, where, null, null);
            if (cursor1 != null) {
                for (cursor1.moveToFirst(); !cursor1.isAfterLast(); cursor1.moveToNext()) {
                    name = cursor1.getString(1);
                }
            }

        }
        english_min.setText(String.valueOf(english));
        english_min_name.setText(name);
    }

    private void getEnglishMax() {
        int english=0;
        String name=null;
        Uri uri=Uri.parse("content://com.sql/score");
        Uri uri1=Uri.parse("content://com.sql/student");
        Cursor cursor=getContentResolver().query(uri,null,null,null,"english "+"desc");

        if (cursor!=null) {
            cursor.moveToFirst();
            english = cursor.getInt(2);
            String stuno=cursor.getString(0);
            String where="stuno="+stuno;
            Cursor cursor1=getContentResolver().query(uri1,null,where,null,null);
            if (cursor1!=null){
                for (cursor1.moveToFirst();!cursor1.isAfterLast();cursor1.moveToNext()){
                    name=cursor1.getString(1);
                }
            }

        }
        english_max.setText(String.valueOf(english));
        english_max_name.setText(name);

    }

    private void getEnglishAverage() {
        Uri uri=Uri.parse("content://com.sql/score");
        Cursor cursor=getContentResolver().query(uri,null,null,null,null);
        int total=0;
        if (cursor!=null){
            for (cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
                total+=cursor.getInt(2);
            }

        }
        float result= (float) (1.0*total/cursor.getCount());
        english_average.setText(String.valueOf(result));


    }

    private void getAndroidMin() {
        int android = 0;
        String name = null;
        Uri uri = Uri.parse("content://com.sql/score");
        Uri uri1 = Uri.parse("content://com.sql/student");
        Cursor cursor = getContentResolver().query(uri, null, null, null, "android " + "asc");

        if (cursor != null) {
            cursor.moveToFirst();
            android = cursor.getInt(1);
            String stuno = cursor.getString(0);
            String where = "stuno=" + stuno;
            Cursor cursor1 = getContentResolver().query(uri1, null, where, null, null);
            if (cursor1 != null) {
                for (cursor1.moveToFirst(); !cursor1.isAfterLast(); cursor1.moveToNext()) {
                    name = cursor1.getString(1);
                }
            }

        }
                    android_min.setText(String.valueOf(android));
                    android_min_name.setText(name);

    }

    private void getAndroidMax() {
        int android=0;
        String name=null;
        Uri uri=Uri.parse("content://com.sql/score");
        Uri uri1=Uri.parse("content://com.sql/student");
        Cursor cursor=getContentResolver().query(uri,null,null,null,"android "+"desc");

        if (cursor!=null) {
            cursor.moveToFirst();
            android = cursor.getInt(1);
            String stuno=cursor.getString(0);
            String where="stuno="+stuno;
            Cursor cursor1=getContentResolver().query(uri1,null,where,null,null);
            if (cursor1!=null){
                for (cursor1.moveToFirst();!cursor1.isAfterLast();cursor1.moveToNext()){
                    name=cursor1.getString(1);
                }
            }

        }
        android_max.setText(String.valueOf(android));
        android_max_name.setText(name);

    }

    private void getAndroidAverage() {
        Uri uri=Uri.parse("content://com.sql/score");
       Cursor cursor=getContentResolver().query(uri,null,null,null,null);
       int total=0;
       if (cursor!=null){
           for (cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
                      total+=cursor.getInt(1);
           }

       }
        float result= (float) (1.0*total/cursor.getCount());
        android_average.setText(String.valueOf(result));
    }

    private void initBunder() {
               android_average=findViewById(R.id.android_average);
               android_max=findViewById(R.id.android_max);
               android_max_name=findViewById(R.id.android_max_name);
               android_min=findViewById(R.id.android_min);
               android_min_name=findViewById(R.id.android_min_name);
               english_average=findViewById(R.id.english_average);
               english_max=findViewById(R.id.english_max);
               english_max_name=findViewById(R.id.english_max_name);
               english_min=findViewById(R.id.english_min);
               english_min_name=findViewById(R.id.english_min_name);
               total_score=findViewById(R.id.total_score);
               total_max=findViewById(R.id.total_max);
               total_max_name=findViewById(R.id.total_max_name);
               total_min=findViewById(R.id.total_min);
               total_min_name=findViewById(R.id.total_min_name);
    }
}
